package love.yuanmengrobot.attendancesystem.dao.SQL;
/*
 *   @Studio:CHJY · 源梦科技
 *   @Author:CHJY创幻
 *   @Ide:IntelliJ IDEA
 *   @Creation time:2021/6/20 21:11
 */

import love.yuanmengrobot.attendancesystem.entity.Attendancemain;
import love.yuanmengrobot.attendancesystem.entity.Attendancestudent;

public class SqlString {

    //教师注册
    public static String insertTeacherMember(String id, String paw ,String name) {

        return " insert into attendancesystem.member(loginid, password, permission, name, sex, phone, qq) values (" + id + ", " + paw + ",0, '"+name+"', null, null, null)";
    }

    //学生注册
    public static String insertStudentMember(String id, String paw,String name) {

        return " insert into attendancesystem.member(loginid, password, permission, name, sex, phone, qq) values (" + id + ", " + paw + ",1, '"+name+"', null, null, null)";
    }
    //登录
    public static  String selectMember(String id,String paw,String perm){

        return "select memberid, loginid, password, permission, name, sex, phone, qq from attendancesystem.member where loginid = "+id+" and "+" password="+paw+" and permission="+perm;
    }
    //创建班级
    public static  String insertAddClazz(String clazzname,String memberid){

        return "INSERT INTO `attendancesystem`.`clazz` (`clazzname`, `memberid`,`createTime`) VALUES ( '"+clazzname+"', '"+memberid+"',sysdate())";
    }
    //加入班级
    public static  String insertClazz(String clazzid,String clazzname,String memberid){

        return "INSERT INTO `attendancesystem`.`clazz` (`clazzid`,`clazzname`, `memberid`,`createTime`) VALUES ('"+clazzid+"', '"+clazzname+"', '"+memberid+"',sysdate())";
    }
    //查询班级，一条
    public static String selectClazzNameById( String clazzid){
        return "select clazzname from `attendancesystem`.`clazz` where `attendancesystem`.`clazz`.`clazzid`="+clazzid+" LIMIT 1";
    }
    //查询教师拥有的班级
    public static String selectTClazzById( String memberid){
        return "SELECT * FROM attendancesystem.clazz where memberid="+memberid;
    }
    //查询xx班级拥有的成员
    public  static  String selectClazzMember(String clazzid){
        return "select  name,loginid,permission from member m,clazz c where m.memberid=c.memberid and clazzid="+clazzid+" order by permission";
    }
    //发起签到
    public static String insertAttendanceMain(Attendancemain attendancemain){
        return "INSERT INTO attendancemain (`atname`, `clazzid`, `memberid`, `attype`, `key`, `atstarttime`) VALUES ('"+attendancemain.getAtname()+"', '"+attendancemain.getClazzid()+"', '"+attendancemain.getMemberid()+"', '"+attendancemain.getAttype()+"', '"+attendancemain.getKey()+"', sysdate())";
    }
    //结束签到
    public static String updateAttendanceMain(String name ,String id){
        return "UPDATE `attendancesystem`.`attendancemain` SET `atendtime` = sysdate() WHERE (attendancemain.atname='"+name+"' and attendancemain.clazzid='"+id+"' )";
    }


    //查询id以及atendtime是否为null，
    public static String selectAttendanceMainNULL( String key){
//        return "select attendancemain.atid, attendancemain.atendtime from attendancesystem.attendancemain where (attendancemain.atname='"+key+"' and attendancemain.clazzid='"+clazzid+"')";//这二值唯一
        return "select attendancemain.atid, attendancemain.atendtime from attendancesystem.attendancemain where (attendancemain.key='"+key+"' )";//这值唯一
    }
//    //查找签到id
////    select attendancemain.atid from attendancesystem.attendancemain where (attendancemain.clazzid='11' and attendancemain.memberid='16'  and  attendancemain.key='2314' and attendancemain.atendtime is null)
//    public static String selectAttendanceMainId( String name ,String id){
//        return "select attendancemain.atid from attendancesystem.attendancemain where (attendancemain.atname='"+name+"' and attendancemain.clazzid='"+id+"'  )";//这二值唯一
//    }
    //学生进行签到
    public static String insertAttendanceStudent(Attendancestudent attendancestudent){
        return "INSERT INTO `attendancesystem`.`attendancestudent` (`atsid`, `atsmemberid`, `address`, `atstime`, `atsstate`) VALUES ('"+attendancestudent.getAtsid()+"', '"+attendancestudent.getAtsmemberid()+"', '"+attendancestudent.getAddress()+"', sysdate(), '"+attendancestudent.getAtsstate()+"')";
    }

    //查询考勤数据
    public static String selectCQ(String clazzid){

        return "SELECT a.atname ,count(*) FROM attendancemain a left join attendancestudent b on a.atid=b.atsid where a.clazzid="+clazzid+" group by a.atname ";
    }
    //学生查询自己的考勤情况
    public static String selectAttendanceByMemberId(String memberid){

        return "SELECT b.atname,a.atstime,a.atsstate FROM attendancesystem.attendancestudent a ,attendancemain b where a.atsid=b.atid and a.atsmemberid="+memberid+" order by atstime desc";
    }

    public static String selectMember(String memberid){
        return "select * from member where memberid="+memberid;
    }


}
